package com.guoli.sms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.guoli.sms.dao.IDeptDao;
import com.guoli.sms.vo.Dept;

public class DeptDaoImpl implements IDeptDao {

	@Override
	public List<Dept> getDepts(Connection conn) throws SQLException {
		String sql = "select id, d_name from t_b_dept order by id";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		List<Dept> depts = new ArrayList<Dept>();
		while (rs.next()) {
			Dept dept = new Dept();
			dept.setId(rs.getInt(1));
			dept.setD_name(rs.getString(2));
			depts.add(dept);
		}
		return depts;
	}

	@Override
	public Dept getDeptById(int id, Connection conn) throws SQLException {
		String sql = "select d_name from t_b_dept where id=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		Dept dept = null;
		while (rs.next()) {
			dept = new Dept();
			dept.setId(id);
			dept.setD_name(rs.getString(1));
		}
		return dept;
	}

	@Override
	public void addDept(String d_name, Connection conn) throws SQLException {
		String sql = "insert into t_b_dept(d_name) values(?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, d_name);
		pstmt.executeUpdate();
	}

	@Override
	public int getDeptByName(String d_name, Connection conn) throws SQLException {
		String sql = "select id from t_b_dept where d_name = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, d_name);
		ResultSet rs = pstmt.executeQuery();
		int id = -1;
		if(rs.next()){
			id = rs.getInt(1);
		}
		return id;
	}

}
